sql command
saixiii

Database SQL – Transaction

อย่างที่เราทราบกันว่าฐานข้อมูล หรือ database นอกจากใช้สำหรับ query หรือ ทำ SQL Select statement เพื่อดึงข้อมูลจาก table ได้แล้ว เราสามารถทำ SQL DML เพื่อเพิ่ม ลด หรือ ปรับเปลี่ยนข้อมูลภายในตารางได้ด้วย ซึ่งการเปลี่ยนข้อมูลภายใน table นี้แหละที่เราเรียกว่า transaction โดย database ต้องมีการควบคุม transaction เหล่านี้ให้ทำงานตามลำดับที่ user กำหนด เช่นการทำ SQL Insert statement ก่อน SQL Update statement แล้วค่อย SQL Delete statement ต้องทำงานตามลำดับเพื่อป้องกัน ไม่ให้คำสั่ง error และข้อมูลสุดท้ายมีความถูกต้อง ซึ่งในบางครั้งเราอาจจะทำการ grouping คำสั่งเหล่าเข้ามาเป็นส่วนหนึ่งใน transaction เดียวกันเพื่อให้ทำงานทีเดียว

 

คุณสมบัติของ Transaction

Transaction จะต้องมีคุณสมบัติตามหลักการ ACID 4 อย่างดังนี้

  • Atomicity − คือ การที่แต่ละ transacion ต้อง “all or nothing”  หมายถึง ถ้ามีกระบวนการใดหรือส่วนหนึ่งส่วนใด fail ทั้งหมดของ transaction นั้นมีค่าเป็น fail และ database จะยกเลิกการเปลี่ยนแปลงที่เกิดจาก transaction นั้น
  • Consistency − คือคุณสมบัตที่จะต้องแน่ใจได้ว่า ไม่ว่า transaction จะทำถึงกระบวนการไหน ข้อมูลจะต้องถูกเขียนลงบน database อย่างถูกต้องตามกฎที่ตั้งไว้
  • Isolation − คือคุณสมบัติที่จะมั่นใจว่า ทุกๆผลลัพธ์จากการทำงานของ transactions จะถูกต้อง ต่อให้กระบวกการของ transactions ก่อนจะไม่สมบูรณ์ ก็จะไม่มีผลกระทบต่อ transactions อื่นๆ
  • Durability − คือ คุณสมบัติที่เมื่อใดก็ตามที่ transaction มีการ “committed” ข้อมูล transaction นั้นจะต้องยังคงอยู่ครบถ้วน ถึงแม้จะเกิดไฟดับ หรือ ระบบล่มหลังจากนั้น

 

การควบคุม Transaction (Transaction Control)

คำสั่งที่ทำ Transaction control จะใช้งานเฉพาะกับ SQL DML Command เช่น INSERT, UPDATE และ DELETE เท่านั้น และจะไม่สามารถใช้งานกับการทำ SQL Create table หรือ SQL Drop table เพราะว่าคำสั่งทั้งคู่นี้มีการยืนยันหรือ commit ให้ทันทีบน database

  • COMMIT − ยืนยันการเปลี่ยนแปลงข้อมูล
  • ROLLBACK − ดึงข้อมูลเก่าก่อนหน้ากลับมา จากจุด savepoint
  • SAVEPOINT − กำหนดจุดของข้อมูล ที่ให้ rollback ข้อมูลกลับมา
  • SET TRANSACTION − กำหนดคุณสมบัติของ transaction

 

1. คำสั่ง COMMIT

COMMIT command เป็นคำสั่งเพื่อบอกกับฐานข้อมูลว่า transaction ที่เกิดขึ้นก่อนหน้านี้ได้รับการยืนยันแล้วว่าให้ save ลงสูง database ได้ โดยการสั่ง COMMIT จะถือว่า transactions ทั้งหมดที่เกิดขึ้นก่อน การ COMMIT หรือ ROLLBACK ล่าสุด ได้รับการยินยอมให้ปรับเปลี่ยนข้อมูลที่ถูกเก็บอยู่ใน table ปัจจุบัน

รูปแบบ commit (Syntax)

COMMIT;

ตัวอย่าง commit

จากข้อมูล table CUSTOMERS

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

ทำการสั่ง SQL Delete ข้อมูลบรรทัดที่ AGE มีค่าเท่ากับ 25

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> COMMIT;

หลังจาก commit แล้ว ผลลัพธ์จะถูก save ลงสู่ table  และจะพบว่าข้อมูล ID = 2 และ ID = 4 ที่มี AGE = 25 หายไปจากข้อมูล table แล้ว

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

 

2. คำสั่ง ROLLBACK

ROLLBACK command เป็นคำสั่งเพื่อยกเลิกการทำ transaction SQL DML ที่เกิดขึ้นจากการทำ INSERT, UPDATE และ DELETE ก่อนหน้านี้ที่ยังไม่มีการ commit หรือ พูดอีกนัยหนึ่งคือย้อนกลับไปที่ข้อมูลที่มีการ COMMIT หรือ ROLLBACK ล่าสุด โดยยกเลิก transaction ที่เกิดขึ้นระหว่างช่วงนั้น

รูปแบบ rollback (Syntax)

ROLLBACK;

ตัวอย่าง rollback

จากข้อมูล table CUSTOMERS

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

ทำการสั่ง SQL Delete ข้อมูลบรรทัดที่ AGE มีค่าเท่ากับ 25

SQL> DELETE FROM CUSTOMERS
   WHERE AGE = 25;
SQL> ROLLBACK;

หลังจาก rollback แล้ว ผลลัพธ์จะยกเลิก transaction delete ที่เกิดขึ้นก่อนหน้า และแสดงข้อมูลที่มีการ commit ล่าสุดแทน ซึ่งก็จะเป็นข้อมูลตั้งต้นเดิม

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

 

3. คำสั่ง SAVEPOINT

SAVEPOINT command เป็นคำสั่งที่ระบุจุดของการทำ transaction เมื่อมีการสั่ง ROLLBACK transaciton กลับไปยังจุด SAVEPOINT โดยที่ไม่ต้องย้อนกลับไปทั้งหมดที่จุดตั้งต้น

รูปแบบการสร้าง savepoint (Syntax)

คำสั่งนี้เพียงแค่สร้างจุด savepoint ระหว่างช่วงของ transaction เพื่อให้คำสั่ง rollback สามารถยกเลิก transaction ที่เกิดหลังจากนั้น กลับมาสู่จุด savepoint ได้

SAVEPOINT SAVEPOINT_NAME;

รูปแบบ rollback กลับมา savepoint (Syntax)

ROLLBACK TO SAVEPOINT_NAME;

ตัวอย่าง savepoint

จากข้อมูล table CUSTOMERS

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

ทำการสร้างจุด savepoint แต่ละช่วงของ transaction delete

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

ทำการ rollback ไปยังจุด savepoint SP2 ที่มีการทำ delete ข้อมูล ID=1 เพียง transaction เดียว

SQL> ROLLBACK TO SP2;
Rollback complete.

ผลลัพธ์ที่ได้ จะเห็นว่ามีเพียง ID=1 เท่านั้นที่ถูกลบออกไป

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

 

4. คำสั่ง RELEASE SAVEPOINT

RELEASE SAVEPOINT command เป็นคำสั่งที่ใช้ยกเลิก SAVEPOINT ที่มีการสร้างขึ้น

รูปแบบการยกเลิก savepoint หรือ release savepoint (Syntax)

RELEASE SAVEPOINT SAVEPOINT_NAME;

หลักจากสั่ง release savepoint แล้ว เราจะไม่สามารถ rollback กลับไปจุด savepoint ดังกล่าวได้อีก

 

5. คำสั่ง SET TRANSACTION

SET TRANSACTION คือคำสั่งที่ใช้ตั้งแต่เริ่ม initial database เพื่อระบุคุณสมบัติของ transaction ที่จะใช้งานได้ เช่น read only หรือ read write

รูปแบบ set transaction (Syntax)

SET TRANSACTION [ READ WRITE | READ ONLY ];

 

 

Reference:

รวมคำสั่ง SQL Command พื้นฐานเบื้องต้น

Database, Oracle, MySQL

 

 

Author: Suphakit Annoppornchai

Credit: https://saixiii.com, https://www.tutorialspoint.com

Leave a Reply